package com.bw.Dao;

import com.bw.bean.Sugar1;
import com.bw.bean.Sugar2;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface SugarDao {
    @Select("select c2.name,sum(o.final_total_amount) `value` from order_info o \n" +
            "LEFT JOIN order_detail d ON o.id=d.order_id\n" +
            "LEFT JOIN sku_info s ON d.sku_id=s.id\n" +
            "LEFT JOIN base_category3 c3 ON s.category3_id=c3.id\n" +
            "LEFT JOIN base_category2 c2 ON c3.category2_id=c2.id \n" +
            "GROUP BY c2.`name`")
    List<Sugar1> getList1();

    @Select(" select \n" +
            " concat(substring(u.birthday,3,1),'0后') `name`,sum(o.final_total_amount) `value` \n" +
            " from order_info o\n" +
            " LEFT JOIN user_info u ON o.user_id=u.id\n" +
            " GROUP BY concat(substring(u.birthday,3,1),'0后')")
    List<Sugar2> getList2();

    @Select("select yys'name',sum(final_total_amount) `value` from order_info where yys is not null GROUP BY yys")
    List<Sugar1> getList3();
    @Select("select t.`name`,\n" +
            "IF(u.gender='F','男','女') gender,\n" +
            "sum(o.final_total_amount) `value`\n" +
            "from order_info o\n" +
            "LEFT JOIN user_info u ON o.user_id=u.id \n" +
            "LEFT JOIN order_detail d ON o.id=d.order_id\n" +
            "LEFT JOIN sku_info s ON d.sku_id=s.id \n" +
            "LEFT JOIN base_trademark t ON s.tm_id=t.id \n" +
            "GROUP BY t.`name`,u.gender")
    List<Sugar2> getList4();
}
